-- =============================================
-- This is a stored procedure to get future publish path.
--
-- Modifications
-- 06/05/2003  E.Tatsui   Coverted from CFML.
-- =============================================

CREATE                     PROCEDURE [dbo].[amsp_CMGetFuturePublishPath]
    @InContentID numeric,
  @InContentFileID numeric = 0,
  @InMicrositeID numeric = 0,
  @OutPublishPath varchar(255) OUTPUT
AS
BEGIN

  DECLARE
    @Name varchar(255),
    @TemplatePath varchar(255),
    @NavContentGroupInd char(1),
    @URLSafeName varchar(255),
    @MembersOnlyFlag char(1),
    @ShowInTemplateFlag char(1),
    @FileName varchar(255),
    @l_TemplatePath varchar(255),
    @l_PublishPath varchar(255),
    @FilePath varchar(255),
    @NavMenuID numeric,
    @NavContentDisplayPath varchar(255),
    @GECodePath varchar(255),
    @UseAspNetTemplateFlag char(1),
    @NextURLChar char(1),
    @URLParams varchar(255)

    SET @URLParams = ''

    SELECT @GECodePath = Value
      FROM System_Variable
     WHERE Name = 'GECodePath'

    -- If ContentFileID is passed, figure out the location.
    IF @InContentFileID > 0 BEGIN
      SELECT @Name = a.Name,
             @NavContentGroupInd = a.NavContentGroupInd,
             @TemplatePath = a.TemplatePath,
             @MembersOnlyFlag = b.MembersOnlyFlag,
             @ShowInTemplateFlag = b.ShowInTemplateFlag,
             @FileName = c.FileName,
             @FilePath = a.FilePath,
             @NavContentDisplayPath = IsNull(w.NavContentDisplayPath,''),
             @UseAspNetTemplateFlag = w.UseAspNetTemplateFlag
        FROM Nav_Menu a WITH (NOLOCK),
             Content b WITH (NOLOCK),
             Content_File c WITH (NOLOCK), Website w WITH (NOLOCK)
       WHERE b.ContentID = @InContentID
         AND b.NavMenuID = a.NavMenuID
         AND a.WebsiteKey = w.WebsiteKey
         AND b.ContentID = c.ContentID
         AND c.ContentFileID = @InContentFileID
      
      IF (@UseAspNetTemplateFlag = 'N') BEGIN
          IF @NavContentGroupInd = 'C' AND @ShowInTemplateFlag = 'Y'
            SET @l_TemplatePath = @GECodePath + 'TemplateRedirect.cfm?Template='
          ELSE IF @ShowInTemplateFlag = 'N'
            SET @l_TemplatePath = @GECodePath + 'AMTemplate.cfm?Section=' + @Name + '&Template='
          ELSE
            SET @l_TemplatePath = @GECodePath + 'Template.cfm?Section=' + @Name + '&Template='
      
          IF @MembersOnlyFlag = 'Y'
            SET @l_PublishPath = @l_TemplatePath + '/MembersOnly.cfm&ContentFileID=' + CONVERT(varchar(20),@InContentFileID)
          ELSE IF @NavContentGroupInd = 'C' AND @MembersOnlyFlag = 'N'
            SET @l_PublishPath = @l_TemplatePath + '/CM/ContentDisplay.cfm&ContentFileID=' + CONVERT(varchar(20),@InContentFileID)
          ELSE BEGIN
            SET @l_PublishPath = '/' + REPLACE(@NavContentDisplayPath,'\','/') + @FilePath
            SET @l_PublishPath = @l_PublishPath + @FileName
           END
      END
      ELSE BEGIN--- ASP.NET Template
          SET @NextURLChar = '?'
          IF @NavContentGroupInd = 'C' AND @ShowInTemplateFlag = 'Y'BEGIN
            SET @l_TemplatePath = @GECodePath + 'ContenManagerNet/TemplateRedirect.aspx?Template=/ContentManagerNet/'
            SET @NextURLChar = '&'
          END
          ELSE IF @ShowInTemplateFlag = 'N' BEGIN
            SET @l_TemplatePath = @GECodePath + 'ContentManagerNet/'
            SET @URLParams = '&Section=' + @Name + '&NoTemplate=1'
          END
          ELSE BEGIN
            SET @l_TemplatePath = @GECodePath + 'ContentManagerNet/'
            SET @URLParams = '&Section=' + @Name
          END
          IF @MembersOnlyFlag = 'Y'
            SET @l_PublishPath = @l_TemplatePath + 'MembersOnly.aspx' + @NextURLChar + 'ContentFileID=' + CONVERT(varchar(20),@InContentFileID) + @URLParams
          ELSE IF @NavContentGroupInd = 'C' AND @MembersOnlyFlag = 'N'
            SET @l_PublishPath = @l_TemplatePath + 'ContentDisplay.aspx' + @NextURLChar + 'ContentFileID=' + CONVERT(varchar(20),@InContentFileID) + @URLParams
          ELSE BEGIN
            SET @l_PublishPath = '/' + REPLACE(@NavContentDisplayPath,'\','/') + @FilePath
            SET @l_PublishPath = @l_PublishPath + @FileName
           END
    END
    END
    ELSE BEGIN -- Otherwise, use asmp_CMGetFuseURL sp.  
      SELECT @NavMenuID = NavMenuID
        FROM Content WITH (NOLOCK)
       WHERE ContentID = @InContentID
      EXEC amsp_CMGetFuseURL @NavMenuID, @InContentID, NULL, @l_PublishPath OUTPUT

      IF LEFT(UPPER(@l_PublishPath),4) <> 'HTTP' AND LEFT(UPPER(@l_PublishPath),1) <> '/'
        SET @l_PublishPath = '/' + @l_PublishPath
    END
      
  SET @OutPublishPath = @l_PublishPath
END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMGetFuturePublishPath] TO [IMIS]
GO